package com.piece.admin.service;

import com.alibaba.fastjson.JSON;
import com.piece.admin.model.TbSysDept;
import com.piece.admin.model.TbSysUser;
import com.piece.admin.repository.TbSysUserRepository;
import com.piece.core.framework.constant.ExceptionConstants;
import com.piece.core.framework.constant.FrameWorkConstants;
import com.piece.core.framework.enums.StatusType;
import com.piece.core.framework.exception.ErrorCode;
import com.piece.core.framework.exception.FrameWorkException;
import com.piece.core.framework.support.bootstrap.DataTable;
import com.piece.core.framework.util.collection.CollectionUtil;
import com.piece.core.framework.util.collection.MapUtil;
import com.piece.core.framework.util.object.ObjectUtil;
import com.piece.core.framework.support.bootstrap.QueryTable;
import com.piece.core.framework.support.convert.Convert;
import com.piece.core.framework.util.string.StringUtil;
import com.piece.core.log.annotation.AuditDiffDetail;
import com.piece.core.log.annotation.AuditLog;
import com.piece.core.log.enums.AuditLogType;
import com.piece.member.service.UmsMemberService;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 用户 业务层处理
 */
@Service
@Transactional
public class TbSysUserService {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    private TbSysUserRepository tbSysUserRepository;

    @Resource
    private TbSysDeptService tbSysDeptService;

    @Resource
    private UmsMemberService umsMemberService;

    /**
     * 新增用户信息
     */
    @AuditLog(table = "TbSysUser", operationType = AuditLogType.INSERT, bizId = "#{#user.userId}", message="新增[#{#user.userName}]用户信息")
    @AuditDiffDetail(sourceProxy = "#{@tbSysUserService.findById(#user.userId?0)}", targetProxy = "#{#user}")
    public TbSysUser insertWithPostRole(TbSysUser user) {
        // 新增用户信息
        user = tbSysUserRepository.insert(user);
        if (StringUtil.isNotEmpty(user.getPostIds())) {
            // 新增用户岗位关联
            insertUserPost(user);
        }
        if (StringUtil.isNotEmpty(user.getRoleIds())) {
            // 新增用户与角色管理
            insertUserRole(user);
        }
        // 维护会员信息表
        umsMemberService.bindByUser(user);
        return user;
    }

    /**
     * 修改用户信息,更新用户岗位与角色
     */
    @AuditLog(table = "TbSysUser", operationType = AuditLogType.UPDATE, bizId = "#{#user.userId}", message="修改[#{#user.userName}]用户信息")
    @AuditDiffDetail(sourceProxy = "#{@tbSysUserService.findById(#user.userId)}", targetProxy = "#{#user}")
    public void updateWithPostRole(TbSysUser user) {
        // 删除用户与岗位关联
        deleteUserPost(Arrays.asList(user.getUserId()));
        if (StringUtil.isNotEmpty(user.getPostIds())) {
            // 新增用户与岗位管理
            insertUserPost(user);
        }
        // 删除用户与角色关联
        deleteUserRole(Arrays.asList(user.getUserId()));
        if (StringUtil.isNotEmpty(user.getRoleIds())) {
            // 新增用户与角色管理
            insertUserRole(user);
        }
        tbSysUserRepository.update(user);
        // 维护会员信息表
        umsMemberService.bindByUser(user);
    }

    /**
     * 修改用户个人详细信息
     */
    @AuditLog(table = "TbSysUser", operationType = AuditLogType.UPDATE, bizId = "#{#user.userId}", message="更新[#{#user.userName}]个人信息")
    @AuditDiffDetail(sourceProxy = "#{@tbSysUserService.findById(#user.userId)}", targetProxy = "#{#user}")
    public void updateUserInfo(TbSysUser user) {
        tbSysUserRepository.update(user);
    }

    public void updateUserResume(Long userId, String resumeId) {
        jdbcTemplate.execute("delete from `tb_sys_user_resume` where `user_id`=" + userId);

        if (StringUtil.isNotEmpty(resumeId)) {
            StringBuilder insert = new StringBuilder("insert into `tb_sys_user_resume` (`user_id`, `file_id`) values ");
            Arrays.stream(resumeId.split(FrameWorkConstants.SPLIT_PARAM)).spliterator().forEachRemaining(resume -> {
                insert.append("(")
                        .append(userId)
                        .append(",'" + resume + "'")
                        .append("), ");
            });

            String sql = (String) JSON.toJSON(insert);
            if (sql.endsWith(", ")) {
                sql = sql.substring(0, sql.length() - 2);
                jdbcTemplate.execute(sql);
            }
        }
    }

    /**
     * 新增用户岗位信息
     */
    public void insertUserPost(TbSysUser user) {
        // 新增用户与岗位
        if (StringUtil.isNotEmpty(user.getPostIds())) {
            StringBuilder insert = new StringBuilder("insert into `tb_sys_user_post` (`user_id`, `post_id`) values ");

            Arrays.asList(Convert.toLongArray(user.getPostIds())).stream().forEach(postId -> {
                insert.append("(")
                        .append(user.getUserId())
                        .append(", ")
                        .append(postId)
                        .append("), ");
            });
            String sql = (String) JSON.toJSON(insert);
            if (sql.endsWith(", ")) {
                sql = sql.substring(0, sql.length() - 2);
                tbSysUserRepository.executeSql(sql);
            }
        }
    }

    /**
     * 删除用户岗位信息
     */
    public void deleteUserPost(List<Long> userId) {
        tbSysUserRepository.executeSql("delete from `tb_sys_user_post` where `user_id` in(" + StringUtil.convert2Sql(userId.toArray()) + ") ");
    }

    /**
     * 新增用户角色信息
     */
    public void insertUserRole(TbSysUser user) {
        // 新增用户与角色
        if (StringUtil.isNotEmpty(user.getRoleIds())) {
            StringBuilder insert = new StringBuilder("insert into `tb_sys_user_role` (`user_id`, `role_id`) values ");

            Arrays.asList(Convert.toLongArray(user.getRoleIds())).stream().forEach(roleId -> {
                insert.append("(")
                        .append(user.getUserId())
                        .append(", ")
                        .append(roleId)
                        .append("), ");
            });
            String sql = (String) JSON.toJSON(insert);
            if (sql.endsWith(", ")) {
                sql = sql.substring(0, sql.length() - 2);
                tbSysUserRepository.executeSql(sql);
            }
        }
    }

    /**
     * 删除用户角色信息
     */
    public void deleteUserRole(List<Long> userId) {
        tbSysUserRepository.executeSql("delete from `tb_sys_user_role` where `user_id` in(" + StringUtil.convert2Sql(userId.toArray()) + ") ");
    }

    /**
     * 修改用户密码
     */
    @AuditLog(table = "TbSysUser", operationType = AuditLogType.UPDATE, bizId = "#{#userId}", message="修改个人密码")
    @AuditDiffDetail(fileName = "password", sourceProxy = "#{@tbSysUserService.findById(#userId)}", targetProxy = "#{#password}")
    public void resetPassword(long userId, String password) {
        tbSysUserRepository.updateByProperties("userId", userId, "password", password);
    }

    /**
     * 通过用户ID删除用户
     */
    @AuditLog(table = "TbSysUser", operationType = AuditLogType.DELETE, bizId = "#{#userId}", message="删除[#{#userId}]用户信息与会员信息")
    public void delete(Long userId) {
        // 删除用户与角色关联
        deleteUserRole(Arrays.asList(userId));
        // 删除用户与岗位表
        deleteUserPost(Arrays.asList(userId));
        umsMemberService.deleteByUser(Arrays.asList(userId));
        tbSysUserRepository.delete(userId);
    }

    /**
     * 批量删除用户信息
     */
    @AuditLog(table = "TbSysUser", operationType = AuditLogType.DELETE, bizId = "#{#ids}", message="删除[#{#ids}]用户信息与会员信息")
    public int deleteByIds(String ids) {
        List<Long> param_in = Arrays.asList(Convert.toLongArray(ids));
        // 删除用户与岗位表
        deleteUserPost(param_in);
        // 删除用户与角色关联
        deleteUserRole(param_in);
        umsMemberService.deleteByUser(param_in);
        return tbSysUserRepository.delete(Convert.toLongArray(ids));
    }

    /**
     * 通过用户ID查询用户
     */
    public TbSysUser findById(Long userId) {
        String sql = "select u.*, d.dept_id as deptId, d.dept_name as deptName from `tb_sys_user` u " +
                " left join `tb_sys_dept` d on u.dept_id = d.dept_id ";
        return tbSysUserRepository.findOneByCondition(sql, "u", MapUtil.builder(new HashMap<String, Object>())
                .put("Q_userId_L_EQ", userId)
                .build());
    }

    /**
     * 查询用户集合
     */
    public List<TbSysUser> findByIds(List<Long> userIds) {
        String sql = "select u.*, d.dept_id as deptId, d.dept_name as deptName from `tb_sys_user` u " +
                " left join `tb_sys_dept` d on u.dept_id = d.dept_id ";
        return tbSysUserRepository.findByCondition(sql, "u", MapUtil.builder(new HashMap<String, Object>())
                .put("Q_userId_L_IN", userIds)
                .put("status", StatusType.NORMAL.getCode())
                .build(), null);
    }

    /**
     * 通过openid查询用户
     */
    public TbSysUser findByOpenId(String openId) {
        HashMap<String, Object> condition = new HashMap<>();
        condition.put("openId", openId);
        condition.put("status", StatusType.NORMAL.getCode());
        return tbSysUserRepository.findOneByCondition(condition);
    }

    /**
     * 通过用户名查询用户
     */
    public TbSysUser findByLoginName(String loginName) {
        HashMap<String, Object> condition = new HashMap<>();
        condition.put("loginName", loginName);
        condition.put("status", StatusType.NORMAL.getCode());
        return tbSysUserRepository.findOneByCondition(condition);
    }

    /**
     * 通过手机号码查询用户
     */
    public TbSysUser findByMobile(String mobile) {
        HashMap<String, Object> condition = new HashMap<>();
        condition.put("mobile", mobile);
        condition.put("status", StatusType.NORMAL.getCode());
        return tbSysUserRepository.findOneByCondition(condition);
    }

    /**
     * 通过邮箱查询用户
     */
    public TbSysUser findByEmail(String email) {
        HashMap<String, Object> condition = new HashMap<>();
        condition.put("email", email);
        condition.put("status", StatusType.NORMAL.getCode());
        return tbSysUserRepository.findOneByCondition(condition);
    }

    /**
     * 获取用户名称
     */
    public String getUserName(long userId) {
        TbSysUser user = findById(userId);
        if (null != user) {
            return user.getUserName();
        }
        return null;
    }

    /**
     * 根据条件查询用户
     */
    public List<TbSysUser> findList(Map<String, Object> condition) {
        String sql = "select u.*, d.dept_id as deptId, d.dept_name as deptName from `tb_sys_user` u " +
                " left join `tb_sys_dept` d on u.dept_id = d.dept_id ";
        return tbSysUserRepository.findByCondition(sql, "u", condition, null);
    }

    /**
     * 根据条件分页查询用户
     *
     * @param queryTable
     * @return
     */
    public DataTable<TbSysUser> findPage(QueryTable queryTable) {
        String hql = "select u.*, d.dept_id as deptId, d.dept_name as deptName from `tb_sys_user` u " +
                " left join `tb_sys_dept` d on u.dept_id = d.dept_id ";
        queryTable.setAlias("u");
        queryTable.setHql(hql);
        return tbSysUserRepository.findPageBySql(queryTable);
    }

    /**
     * 根据条件分页查询角色下用户
     */
    public DataTable<TbSysUser> findRoleUserPage(QueryTable queryTable, long roleId) {
        String hql = "select u.*, d.dept_id as deptId, d.dept_name as deptName from `tb_sys_user` u " +
                " left join `tb_sys_dept` d on u.dept_id = d.dept_id " +
                " left join `tb_sys_user_role` ur on u.user_id = ur.user_id " +
                " left join `tb_sys_role` r on r.role_id = ur.role_id " +
                " where r.role_id = " + roleId;
        queryTable.setHql(hql);
        return tbSysUserRepository.findPageBySql(queryTable);
    }

    public List<TbSysUser> findRoleUser(long roleId) {
        String hql = "select u.*, d.dept_id as deptId, d.dept_name as deptName from `tb_sys_user` u " +
                " left join `tb_sys_dept` d on u.dept_id = d.dept_id " +
                " left join `tb_sys_user_role` ur on u.user_id = ur.user_id " +
                " left join `tb_sys_role` r on r.role_id = ur.role_id " +
                " where r.role_id = " + roleId;
        return tbSysUserRepository.findBySql(hql, null);
    }

    /**
     * 根据条件分页查询非角色下用户
     */
    public DataTable<TbSysUser> findNotRoleUserPage(QueryTable queryTable, long roleId) {
        String hql = "select u.*, d.dept_id as deptId, d.dept_name as deptName from `tb_sys_user` u " +
                " left join `tb_sys_dept` d on u.dept_id = d.dept_id " +
                " where u.user_id not in(select ur.user_id from `tb_sys_user_role` ur where ur.role_id = " + roleId + ") " +
                " and exists(select r.role_id from `tb_sys_role` r where r.role_id = " + roleId +
                " and r.status = '" + StatusType.NORMAL.getCode() + "' )";
        queryTable.setHql(hql);
        return tbSysUserRepository.findPageBySql(queryTable);
    }

    /**
     * 查询角色人员
     *
     * @param roleIds 角色ID集合
     * @return
     */
    public List<TbSysUser> findUserByRoles(List<Long> roleIds) {
        String hql = "select u.* from `tb_sys_user` u left join `tb_sys_user_role` ur on ur.user_id = u.user_id " +
                " where ur.role_id in (?0) and u.status = ?1";
        return tbSysUserRepository.findBySql(hql, new Object[]{roleIds, StatusType.NORMAL.getCode()});
    }

    /**
     * 查询部门所有员工
     */
    public List<TbSysUser> findDeptUser(long deptId) {
        String hql = "select u.*, d.dept_id as deptId, d.dept_name as deptName from `tb_sys_user` u " +
                " left join `tb_sys_dept` d on u.dept_id = d.dept_id " +
                " where u.dept_id in(select id from `tb_sys_dept` where concat(‘,’,ancestors,’,’) like '%," + deptId + ",%' and status=?0 " +
                " and u.status = ?1";
        return tbSysUserRepository.findBySql(hql, new Object[]{StatusType.NORMAL.getCode(), StatusType.NORMAL.getCode()});
    }

    /**
     * 查询角色人员
     *
     * @param roleIds 角色ID集合
     * @param isOrg   是否是机构
     */
    public List<TbSysUser> findUserByDeptRoles(Long deptId, List<Long> roleIds, boolean isOrg) {
        TbSysDept dept = tbSysDeptService.findParentDept(deptId, isOrg);
        String hql = "select u.* from `tb_sys_user` u left join `tb_sys_user_role` ur on ur.user_id = u.user_id " +
                " left join tb_sys_dept d on d.dept_id = u.dept_id and d.dept_id = ?0" +
                " where ur.role_id in (?1)";
        return tbSysUserRepository.findBySql(hql, new Object[]{dept.getDeptId(), roleIds});
    }

    /**
     * 查询部门岗位人员
     *
     * @param deptId  部门ID
     * @param postIds 岗位ID集合
     * @param isOrg   是否是机构
     */
    public List<TbSysUser> findUserByDeptPosts(long deptId, List<Long> postIds, boolean isOrg) {
        TbSysDept dept = tbSysDeptService.findParentDept(deptId, isOrg);
        String hql = "select u.* from `tb_sys_user` u " +
                " left join `tb_sys_user_post` up on up.user_id = u.user_id " +
                " where u.dept_id = ?0 and up.post_id in (?1)";
        return tbSysUserRepository.findBySql(hql, new Object[]{dept.getDeptId(), postIds});
    }

    public String findUserResume(long userId) {
        List<String> result = jdbcTemplate.query("select `file_id` from `tb_sys_user_resume` where `user_id` = " + userId, new RowMapper<String>() {
            @Override
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getString(1);
            }
        });

        return ObjectUtil.isEmpty(result) ? null : result.get(0);
    }

    /**
     * 分组统计查询
     *
     * @param queryTable  查询条件
     * @param targetClass 分组结果类型
     */
    public List findGroup(QueryTable queryTable, Class targetClass) {
        return tbSysUserRepository.findByGroup(queryTable, targetClass);
    }

    /**
     * 校验用户名称是否唯一
     *
     * @param userId    用户ID
     * @param loginName 登录名
     * @return true: 通过 false: 重复
     */
    public boolean checkLoginNameUnique(Long userId, String loginName) {
        String hql = "from TbSysUser where loginName = ?0 and status = ?1";
        Object[] params = new Object[]{loginName, StatusType.NORMAL.getCode()};
        if (null != userId) {
            hql = "from TbSysUser where userId != ?0 and loginName = ?1 and status = ?2";
            params = new Object[]{userId, loginName, StatusType.NORMAL.getCode()};
        }
        long count = tbSysUserRepository.count(hql, params, true);
        return count <= 0;
    }

    /**
     * 校验注册手机是否唯一
     *
     * @param mobile 手机号
     * @return true: 通过 false: 重复
     */
    public boolean checkPhoneUnique(Long userId, String mobile) {
        String hql = "from TbSysUser where mobile = ?0 and status = ?1";
        Object[] params = new Object[]{mobile, StatusType.NORMAL.getCode()};
        if (null != userId) {
            hql = "from TbSysUser where userId != ?0 and mobile = ?1 and status = ?2 ";
            params = new Object[]{userId, mobile, StatusType.NORMAL.getCode()};
        }
        long count = tbSysUserRepository.count(hql, params, true);
        return count <= 0;
    }

    /**
     * 校验email是否唯一
     *
     * @param email 邮箱
     * @return true: 通过 false: 重复
     */
    public boolean checkEmailUnique(Long userId, String email) {
        String hql = "from TbSysUser where email = ?0 and status = ?1";
        Object[] params = new Object[]{email, StatusType.NORMAL.getCode()};
        if (null != userId) {
            hql = "from TbSysUser where userId != ?0 and email = ?1 and status = ?2 ";
            params = new Object[]{userId, email, StatusType.NORMAL.getCode()};
        }
        long count = tbSysUserRepository.count(hql, params, true);
        return count <= 0;
    }

    public String convertIdToName(String ids) {
        String sql = "select `user_name` from `tb_sys_user` where `user_id` in (:userIds)";
        Map<String, Object> param = new HashMap<>();
        param.put("userIds", Convert.toLongList(ids));
        List<String> names = namedParameterJdbcTemplate.queryForList(sql, param, String.class);
        if (names.size() > 0) {
            return StringUtil.join(names, FrameWorkConstants.SPLIT_PARAM);
        }
        return "";
    }

    public void validUser(Set<Long> ids) {
        if (ObjectUtil.isEmpty(ids)) {
            return;
        }

        List<TbSysUser> users = tbSysUserRepository.findByCondition(MapUtil.builder(new HashMap<String, Object>())
                .put("userId", new ArrayList<>(ids))
                .build(), null);
        Map<Long, TbSysUser> userMap = CollectionUtil.convertMap(users, TbSysUser::getUserId);
        ids.forEach(id -> {
            TbSysUser user = userMap.get(id);
            if (null == user) {
                throw new FrameWorkException(new ErrorCode(ExceptionConstants.NOT_EXIST, "用户【" + id + "】"));
            }

            if (!StatusType.NORMAL.getCode().equals(user.getStatus())) {
                throw new FrameWorkException(new ErrorCode(ExceptionConstants.IS_DISABLE, user.getUserName()));
            }
        });
    }

    public void flush() {
        tbSysUserRepository.flush();
    }
}
